package com.css.fxfzaqbz.modules.data.repository.impl;

import com.css.fxfzaqbz.constants.YNEnum;
import com.css.fxfzaqbz.modules.data.entity.GasstationData;
import com.css.fxfzaqbz.modules.data.queryParams.GasstationDataQueryParams;
import com.css.fxfzaqbz.modules.data.repository.GasstationDataRepository;
import com.css.fxfzaqbz.util.PlatformObjectUtils;
import org.springframework.data.domain.Page;
import org.springframework.data.domain.PageImpl;
import org.springframework.data.domain.PageRequest;
import org.springframework.data.domain.Pageable;
import org.springframework.jdbc.core.BatchPreparedStatementSetter;
import org.springframework.jdbc.core.BeanPropertyRowMapper;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.namedparam.BeanPropertySqlParameterSource;
import org.springframework.jdbc.core.namedparam.NamedParameterJdbcTemplate;
import org.springframework.jdbc.core.namedparam.SqlParameterSource;
import org.springframework.jdbc.support.GeneratedKeyHolder;
import org.springframework.jdbc.support.KeyHolder;
import org.springframework.stereotype.Repository;

import javax.annotation.Resource;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.sql.Timestamp;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

@Repository
public class GasstationDataRepositoryImpl implements GasstationDataRepository {
    @Resource
    JdbcTemplate jdbcTemplate;
    @Resource
    NamedParameterJdbcTemplate namedParameterJdbcTemplate;

//    @Override
//    public ElevationVO updateElevation(ElevationVO elevationVO) {
//
//        String sql = "update sys_elevation set elevation_number = :elevationNumber, elevation_band = :elevationBand, elevation_nodatavalue = :elevationNodatavalue, elevation_min = :elevationMin, elevation_max = :elevationMax  where elevation_id = '"+elevationVO.getElevationId()+"'" ;
//
//        SqlParameterSource source = new BeanPropertySqlParameterSource(elevationVO);
//        //拿到新插入的主键值（i）
//        KeyHolder keyHolder = new GeneratedKeyHolder();
//        namedParameterJdbcTemplate.update(sql,source,keyHolder);
//        return elevationVO;
//    }

    @Override
    public GasstationData saveGasstationData(GasstationData gasstationData) {
        String sql = "insert into sys_gasstationdata values(:gsId,:gsNumber,:gsLongitude,:gsLatitude,:gsName,:gsAddress,:gsScale,:gsRiskgrade,:gsStatus,:gsUpdatetime,:gsCreatetime)";

        SqlParameterSource source = new BeanPropertySqlParameterSource(gasstationData);
        //拿到新插入的主键值（i）
        KeyHolder keyHolder = new GeneratedKeyHolder();
        int i = namedParameterJdbcTemplate.update(sql, source, keyHolder);
        return gasstationData;
    }

    @Override
    public Page<GasstationData> getAllGasstationDataParams(GasstationDataQueryParams queryParams) {
        StringBuilder sql = new StringBuilder(" select  g.* from sys_gasstationdata g ");

        StringBuilder orderSql = new StringBuilder(" order by g.gs_id");

        StringBuilder whereSql = new StringBuilder(" where g.gs_status = '" + YNEnum.Y.toString() + "'");

//        if (!PlatformObjectUtils.isEmpty(queryParams.getGsName()) ){
//            whereSql.append(" and g.gs_name like '%" + queryParams.getGsName() + "%'");
//        }
//
//        if (!PlatformObjectUtils.isEmpty(queryParams.getGsAddress()) ){
//           whereSql.append(" and g.gs_address like '%" + queryParams.getGsAddress() + "%'");
//        }

        String wholeSql = sql.append(whereSql).append(orderSql).toString();
        List<GasstationData> list = jdbcTemplate.query(wholeSql, new BeanPropertyRowMapper<>(GasstationData.class));
        Pageable pageable = PageRequest.of(queryParams.getCurPage() - 1, queryParams.getPageSize());

        Page<GasstationData> gasstationData = new PageImpl<>(list, pageable, PlatformObjectUtils.isEmpty(list) ? 0 : list.size());
        return gasstationData;
    }

    @Override
    public GasstationData getGasstationDataById(String id) {
        String sql = "select * from sys_gasstationdata where gs_id = '"+id+"' and gs_status = '"+YNEnum.Y+"'";
        GasstationData gasstationData = jdbcTemplate.queryForObject(sql, new BeanPropertyRowMapper<>(GasstationData.class));
        return gasstationData;
    }

    @Override
    public void deleteGasstationDatasById(List<String> ids, Timestamp timestamp) {
        String sql = "update sys_gasstationdata set gs_status = '" + YNEnum.N + "', gs_updatetime='"+timestamp+"' " + " where gs_id in (:ids)";
        Map map = new HashMap();
        map.put("ids", ids);
        namedParameterJdbcTemplate.update(sql, map);
    }

    @Override
    public List<GasstationData> getAll() {
        return jdbcTemplate.query("select * from sys_gasstationdata where gs_status = '"+YNEnum.Y+"'",new BeanPropertyRowMapper<>(GasstationData.class));
    }

    @Override
    public void importExcel(List<GasstationData> gasstationData) {
        jdbcTemplate.batchUpdate("insert into sys_gasstationdata (gs_id,gs_number,gs_longitude,gs_latitude,gs_name,gs_address,gs_scale,gs_riskgrade,gs_status,gs_createtime)values(?,?,?,?,?,?,?,?,?,?)", new BatchPreparedStatementSetter() {
            @Override
            public int getBatchSize() {
                return gasstationData.size();
            }
            @Override
            public void setValues(PreparedStatement ps, int i) throws SQLException {
                ps.setString(1,gasstationData.get(i).getGsId());
                ps.setString(2,gasstationData.get(i).getGsNumber());
                ps.setFloat(3, gasstationData.get(i).getGsLongitude());
                ps.setFloat(4,gasstationData.get(i).getGsLatitude());
                ps.setString(5,gasstationData.get(i).getGsName());
                ps.setString(6,gasstationData.get(i).getGsAddress());
                ps.setString(7,gasstationData.get(i).getGsScale());
                ps.setString(8,gasstationData.get(i).getGsRiskgrade());
                ps.setString(9,gasstationData.get(i).getGsStatus());
                java.sql.Date stimeDate = new java.sql.Date(gasstationData.get(i).getGsCreatetime().getTime()) ;
                ps.setDate(10,stimeDate);
            }
        });
    }

}
